<?php
// Tim'Roster
// Copyright (c) 2011 Nicolas Nallet <aspgic2@gmail.com>
//
// This file is part of Tim'Roster.
//
// Tim'Roster is free software; you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation; either version 3 of the License, or
// (at your option) any later version.
//
// Tim'Roster is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program; if not, write to the Free Software Foundation,
// Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301  USA

$xml_config = simplexml_load_file('config.xml');

include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/class/Character.php');
include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/class/Logbook.php');
include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/data_access/base_DA.php');
include_once($_SERVER["DOCUMENT_ROOT"].$xml_config->roster_root_directory.'/data_access/guild_DA.php');

class character_DA extends base_DA {

    /** Character Fields Name  **/
    const FL_ID = 'id';
    const FL_API_KEY = 'api_key';
    const FL_ACTIVE = 'active';
    const FL_NAME = 'name';
    const FL_SHARD = 'shard';
    const FL_RACE = 'race';
    const FL_GENDER = 'gender';
    const FL_TITLEID = 'titleid';
    const FL_MONEY = 'money';
    const FL_GRADE = 'grade';
    const FL_FK_GUILD_ID = 'fk_guild_id';
    const FL_CIVILIZATION = 'civilization';
    const FL_CULT = 'cult';
    const FL_FYROS_FAME = 'fyros_fame';
    const FL_KAMI_FAME = 'kami_fame';
    const FL_KARAVAN_FAME = 'karavan_fame';
    const FL_MATIS_FAME = 'matis_fame';
    const FL_TRIBE_DARKENING_SAP_FAME = 'tribe_darkening_sap_fame';
    const FL_TRIBE_LAWLESS_FAME = 'tribe_lawless_fame';
    const FL_TRIBE_LEVIERS_FAME = 'tribe_leviers_fame';
    const FL_TRIBE_MASTER_OF_THE_GOO_FAME = 'tribe_master_of_the_goo_fame';
    const FL_TRIBE_RESTORERS_FAME = 'tribe_restorers_fame';
    const FL_TRIBE_SCORCHERS_FAME = 'tribe_scorchers_fame';
    const FL_TRIBE_WATER_BREAKERS_FAME = 'tribe_water_breakers_fame';
    const FL_TRYKER_FAME = 'tryker_fame';
    const FL_ZORAI_FAME = 'zorai_fame';
    const FL_KAMI_FACTION_POINT = 'kami_faction_point';
    const FL_KARAVAN_FACTION_POINT = 'karavan_faction_point';
    const FL_FYROS_FACTION_POINT = 'fyros_faction_point';
    const FL_MATIS_FACTION_POINT = 'matis_faction_point';
    const FL_TRYKER_FACTION_POINT = 'tryker_faction_point';
    const FL_ZORAI_FACTION_POINT = 'zorai_faction_point';
    const FL_CONSTITUTION = 'constitution';
    const FL_METABOLISM = 'metabolism';
    const FL_INTELLIGENCE = 'intelligence';
    const FL_WISDOM = 'wisdom';
    const FL_STRENGTH = 'strength';
    const FL_WELLBALANCED = 'wellbalanced';
    const FL_DEXTERITY = 'dexterity';
    const FL_WILL = 'will';
    const FL_HITPOINTS = 'hitpoints';
    const FL_STAMINA = 'stamina';
    const FL_SAP = 'sap';
    const FL_FOCUS = 'focus';
    const FL_PLAYED_TIME = 'played_time';
    const FL_LATEST_LOGIN = 'latest_login';
    const FL_LATEST_LOGOUT = 'latest_logout';
    const FL_HAIR_TYPE = 'hair_type';
    const FL_HAIR_COLOR = 'hair_color';
    const FL_TATTOO = 'tattoo';
    const FL_EYES_COLOR = 'eyes_color';
    const FL_GABARIT_HEIGHT = 'gabarit_height';
    const FL_GABARIT_TORSO_WIDTH = 'gabarit_torso_width';
    const FL_GABARIT_ARMS_WIDTH = 'gabarit_arms_width';
    const FL_GABARIT_LEGS_WIDTH = 'gabarit_legs_width';
    const FL_GABARIT_BREAST_SIZE = 'gabarit_breast_size';
    const FL_MORPH1 = 'morph1';
    const FL_MORPH2 = 'morph2';
    const FL_MORPH3 = 'morph3';
    const FL_MORPH4 = 'morph4';
    const FL_MORPH5 = 'morph5';
    const FL_MORPH6 = 'morph6';
    const FL_MORPH7 = 'morph7';
    const FL_MORPH8 = 'morph8';
    const FL_MACE_1H = 'mace_1h';
    const FL_STAFF_1H = 'staff_1h';
    const FL_SPEAR_1H = 'spear_1h';
    const FL_AXE_1H = 'axe_1h';
    const FL_SWORD_1H = 'sword_1h';
    const FL_DAGGER_1H = 'dagger_1h';
    const FL_MACE_2H = 'mace_2h';
    const FL_PIKE_2H = 'pike_2h';
    const FL_AXE_2H = 'axe_2h';
    const FL_SWORD_2H = 'sword_2h';
    const FL_HAND_TO_HAND = 'hand_to_hand';
    const FL_PISTOL = 'pistol';
    const FL_AUTOLAUNCHER = 'autolauncher';
    const FL_LAUNCHER = 'launcher';
    const FL_RIFLE = 'rifle';
    const FL_ELEMENTAL_MAGIC = 'elemental_magic';
    const FL_OFFENSIVE_AFFICTION = 'offensive_affiction';
    const FL_HEALING_MAGIC = 'healing_magic';
    const FL_DEFENSIVE_AFFLICTION = 'defensive_affliction';
    const FL_DESERT_HARVEST = 'desert_harvest';
    const FL_JUNGLE_HARVEST = 'jungle_harvest';
    const FL_LAKE_HARVEST = 'lake_harvest';
    const FL_PRIME_ROOTS_HARVEST = 'prime_roots_harvest';
    const FL_FOREST_HARVEST = 'forest_harvest';
    const FL_CRAFT_HEAVY_ARMOR_BOOT = 'craft_heavy_armor_boot';
    const FL_CRAFT_HEAVY_ARMOR_GLOVE = 'craft_heavy_armor_glove';
    const FL_CRAFT_HEAVY_ARMOR_HELMET = 'craft_heavy_armor_helmet';
    const FL_CRAFT_HEAVY_ARMOR_PANT = 'craft_heavy_armor_pant';
    const FL_CRAFT_HEAVY_ARMOR_SLEEVE = 'craft_heavy_armor_sleeve';
    const FL_CRAFT_HEAVY_ARMOR_VEST = 'craft_heavy_armor_vest';
    const FL_CRAFT_MEDIUM_ARMOR_BOOT = 'craft_medium_armor_boot';
    const FL_CRAFT_MEDIUM_ARMOR_GLOVE = 'craft_medium_armor_glove';
    const FL_CRAFT_MEDIUM_ARMOR_PANT = 'craft_medium_armor_pant';
    const FL_CRAFT_MEDIUM_ARMOR_SLEEVE = 'craft_medium_armor_sleeve';
    const FL_CRAFT_MEDIUM_ARMOR_VEST = 'craft_medium_armor_vest';
    const FL_CRAFT_LIGHT_ARMOR_BOOT = 'craft_light_armor_boot';
    const FL_CRAFT_LIGHT_ARMOR_GLOVE = 'craft_light_armor_glove';
    const FL_CRAFT_LIGHT_ARMOR_PANT = 'craft_light_armor_pant';
    const FL_CRAFT_LIGHT_ARMOR_SLEEVE = 'craft_light_armor_sleeve';
    const FL_CRAFT_LIGHT_ARMOR_VEST = 'craft_light_armor_vest';
    const FL_CRAFT_SHIELD = 'craft_shield';
    const FL_CRAFT_LARGE_SHIELD = 'craft_large_shield';
    const FL_CRAFT_JEWEL_ANKLET = 'craft_jewel_anklet';
    const FL_CRAFT_JEWEL_BRACELET = 'craft_jewel_bracelet';
    const FL_CRAFT_JEWEL_DIADEM = 'craft_jewel_diadem';
    const FL_CRAFT_JEWEL_EARRING = 'craft_jewel_earring';
    const FL_CRAFT_JEWEL_PENDANT = 'craft_jewel_pendant';
    const FL_CRAFT_JEWEL_RING = 'craft_jewel_ring';
    const FL_CRAFT_AXE_1H = 'craft_axe_1h';
    const FL_CRAFT_DAGGER_1H = 'craft_dagger_1h';
    const FL_CRAFT_MACE_1H = 'craft_mace_1h';
    const FL_CRAFT_SPEAR_1H = 'craft_spear_1h';
    const FL_CRAFT_SWORD_1H = 'craft_sword_1h';
    const FL_CRAFT_STAFF_1H = 'craft_staff_1h';
    const FL_CRAFT_AXE_2H = 'craft_axe_2h';
    const FL_CRAFT_MACE_2H = 'craft_mace_2h';
    const FL_CRAFT_PIKE_2H = 'craft_pike_2h';
    const FL_CRAFT_SWORD_2H = 'craft_sword_2h';
    const FL_CRAFT_AMPLI = 'craft_ampli';
    const FL_CRAFT_PISTOL = 'craft_pistol';
    const FL_CRAFT_AUTOLAUNCHER = 'craft_autolauncher';
    const FL_CRAFT_LAUNCHER = 'craft_launcher';
    const FL_CRAFT_RIFLE = 'craft_rifle';
    const FL_JOINED_DATE = 'joined_date';
    const FL_LAST_UPDATE_DATE = 'last_update_date';

    /**
     * (PHP 5)<br/>
     * Get a character id
     * @param string $name
     * @return string
     */
    public function get_character_id($name){
        $sql_cmd = 'SELECT '.character_DA::FL_ID.' FROM '.$this->get_table_prefix().'_character WHERE '.character_DA::FL_NAME.' = '.$this->format_string_to_sql_param($name).'';

//        echo '<br>character_DA->get_character_id : '.$sql_cmd;

        $key = $this->execute_sql_command($sql_cmd, false);

        if (count($key)>0) { return $key[0][0]; }

        return null;
    }


    /**
     * (PHP 5)<br/>
     * Get all character api key in the db
     * @return array of string
     */
    public function get_all_api_key(){
        $sql_cmd = 'SELECT '.character_DA::FL_API_KEY.', '.character_DA::FL_ID.', '.character_DA::FL_NAME.' FROM '.$this->get_table_prefix().'_character';

//        echo '<br>character_DA->get_all_api_key : '.$sql_cmd;

        return $this->execute_sql_command($sql_cmd, false);
    }

    /**
     * (PHP 5)<br/>
     * Get character api key in the db
     * @return array of string
     */
    public function get_character_api_key($user_id){
        $sql_cmd = 'SELECT '.character_DA::FL_API_KEY.', '.character_DA::FL_ID.', '.character_DA::FL_NAME
                    .' FROM '.$this->get_table_prefix().'_character WHERE '.character_DA::FL_ID.' = '.$user_id;

//        echo '<br>character_DA->get_character_api_key : '.$sql_cmd;

        return $this->execute_sql_command($sql_cmd, false);
    }



    /**
     * (PHP 5)<br/>
     * Update grade information in the database
     * @param Guild_Member $member
     * @param int $char_id
     */
    public function update_character_grade($member, $char_id, $id_guild){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_character
                    SET '.character_DA::FL_GRADE.' = '.$member->get_grade_id().'
                    WHERE '.character_DA::FL_ID.' = '.$char_id;

//        echo '<br>character_DA->update_character_grade : '.$sql_cmd;
        
        $this->execute_sql_command($sql_cmd, false);
        Logbook::member_grade_changed($member->get_name(), $member->get_grade_id(), $id_guild);
    }

    /**
     * (PHP 5)<br/>
     * Update character information
     * @param Character $char
     * @param int $id
     */
    public function update_character($char, $id){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_character
                    SET '.character_DA::FL_NAME.' = '.$this->format_string_to_sql_param($char->get_name()).', '.character_DA::FL_SHARD.' = '.$this->format_string_to_sql_param($char->get_shard())
                        .', '.character_DA::FL_RACE.' = '.$this->format_string_to_sql_param($char->get_race()).', '.character_DA::FL_GENDER.' = '.$this->format_string_to_sql_param($char->get_gender()).',
                        '.character_DA::FL_TITLEID.' = '.$this->format_string_to_sql_param($char->get_titleid()).', '.character_DA::FL_MONEY.' = '.$this->format_string_to_sql_param($char->get_money()).', '.character_DA::FL_CIVILIZATION.' = '.$this->format_string_to_sql_param($char->get_civilization()).',
                        '.character_DA::FL_CULT.' = '.$this->format_string_to_sql_param($char->get_cult()).', '.character_DA::FL_FYROS_FAME.' = '.$char->get_fyros_fame().', '.character_DA::FL_KAMI_FAME.' = '.$char->get_kami_fame().', '.character_DA::FL_KARAVAN_FAME.' = '. $char->get_karavan_fame().',
                        '.character_DA::FL_MATIS_FAME.' = '.$char->get_matis_fame().', '.character_DA::FL_TRIBE_DARKENING_SAP_FAME.' = '.$char->get_tribe_darkening_sap_fame().', '.character_DA::FL_TRIBE_LAWLESS_FAME.' = '.$char->get_tribe_lawless_fame().', '.character_DA::FL_TRIBE_LEVIERS_FAME.' = '.$char->get_tribe_leviers_fame().',
                        '.character_DA::FL_TRIBE_MASTER_OF_THE_GOO_FAME.' = '.$char->get_tribe_master_of_the_goo_fame().', '.character_DA::FL_TRIBE_RESTORERS_FAME.' = '.$char->get_tribe_restorers_fame().', '.character_DA::FL_TRIBE_SCORCHERS_FAME.' = '.$char->get_tribe_scorchers_fame().', '.character_DA::FL_TRIBE_WATER_BREAKERS_FAME.' = '.$char->get_tribe_water_breakers_fame().',
                        '.character_DA::FL_TRYKER_FAME.' = '.$char->get_tryker_fame().', '.character_DA::FL_ZORAI_FAME.' = '.$char->get_zorai_fame().', '.character_DA::FL_KAMI_FACTION_POINT.' = '.$char->get_kami_faction_point().', '.character_DA::FL_KARAVAN_FACTION_POINT.' = '.$char->get_karavan_faction_point().',
                        '.character_DA::FL_FYROS_FACTION_POINT.' = '.$char->get_fyros_faction_point().', '.character_DA::FL_MATIS_FACTION_POINT.' = '.$char->get_matis_faction_point().', '.character_DA::FL_TRYKER_FACTION_POINT.' = '.$char->get_tryker_faction_point().', '.character_DA::FL_ZORAI_FACTION_POINT.' = '.$char->get_zorai_faction_point().',
                        '.character_DA::FL_CONSTITUTION.' = '.$char->get_constitution().', '.character_DA::FL_METABOLISM.' = '.$char->get_metabolism().', '.character_DA::FL_INTELLIGENCE.' = '.$char->get_intelligence().', '.character_DA::FL_WISDOM.' = '.$char->get_wisdom().',
                        '.character_DA::FL_STRENGTH.' = '.$char->get_strength().', '.character_DA::FL_WELLBALANCED.' = '.$char->get_wellbalanced().', '.character_DA::FL_DEXTERITY.' = '.$char->get_dexterity().', '.character_DA::FL_WILL.' = '.$char->get_will().',
                        '.character_DA::FL_HITPOINTS.' = '.$char->get_hit_points().', '.character_DA::FL_STAMINA.' = '.$char->get_stamina().', '.character_DA::FL_SAP.' = '.$char->get_sap().', '.character_DA::FL_FOCUS.' = '.$char->get_focus().',
                        '.character_DA::FL_PLAYED_TIME.' = '.$this->format_string_to_sql_param($char->get_played_time())
                        .', latest_login = '.$this->format_date_to_sql_param($char->get_latest_login())
                        .', latest_logout = '.$this->format_date_to_sql_param($char->get_latest_logout())
                        .', '.character_DA::FL_HAIR_TYPE.' = '.$char->get_hair_type().',
                        '.character_DA::FL_HAIR_COLOR.' = '.$char->get_hair_color().', '.character_DA::FL_TATTOO.' = '.$char->get_tattoo().', '.character_DA::FL_EYES_COLOR.' = '.$char->get_eyes_color().', '.character_DA::FL_GABARIT_HEIGHT.' = '.$char->get_gabarit_height().',
                        '.character_DA::FL_GABARIT_TORSO_WIDTH.' = '.$char->get_gabarit_torso_width().', '.character_DA::FL_GABARIT_ARMS_WIDTH.' = '.$char->get_gabarit_arms_width().', '.character_DA::FL_GABARIT_LEGS_WIDTH.' = '.$char->get_gabarit_legs_width().', '.character_DA::FL_GABARIT_BREAST_SIZE.' = '.$char->get_gabarit_breast_size().',
                        '.character_DA::FL_MORPH1.' = '.$char->get_morph1().', '.character_DA::FL_MORPH2.' = '.$char->get_morph2().', '.character_DA::FL_MORPH3.' = '.$char->get_morph3().', '.character_DA::FL_MORPH4.' = '.$char->get_morph4().',
                        '.character_DA::FL_MORPH5.' = '.$char->get_morph5().', '.character_DA::FL_MORPH6.' = '.$char->get_morph6().', '.character_DA::FL_MORPH7.' = '.$char->get_morph7().', '.character_DA::FL_MORPH8.' = '.$char->get_morph8().', '.character_DA::FL_MACE_1H.' = '.$char->get_mace_1h().', '.character_DA::FL_STAFF_1H.' = '.$char->get_staff_1h().', '.character_DA::FL_SPEAR_1H.' = '.$char->get_spear_1h().', '.character_DA::FL_AXE_1H.' = '.$char->get_axe_1h().',
                        '.character_DA::FL_SWORD_1H.' = '.$char->get_sword_1h().', '.character_DA::FL_DAGGER_1H.' = '.$char->get_dagger_1h().', '.character_DA::FL_MACE_2H.' = '.$char->get_mace_2h().', '.character_DA::FL_PIKE_2H.' = '.$char->get_pike_2h().',
                        '.character_DA::FL_AXE_2H.' = '.$char->get_axe_2h().', '.character_DA::FL_SWORD_2H.' = '.$char->get_sword_2h().', '.character_DA::FL_HAND_TO_HAND.' = '.$char->get_hand_to_hand().', '.character_DA::FL_PISTOL.' = '.$char->get_pistol().',
                        '.character_DA::FL_AUTOLAUNCHER.' = '.$char->get_autolauncher().', '.character_DA::FL_LAUNCHER.' = '.$char->get_launcher().', '.character_DA::FL_RIFLE.' = '.$char->get_rifle().', '.character_DA::FL_ELEMENTAL_MAGIC.' = '.$char->get_elemental_magic().',
                        '.character_DA::FL_OFFENSIVE_AFFICTION.' = '.$char->get_offensive_affiction().', '.character_DA::FL_HEALING_MAGIC.' = '.$char->get_healing_magic().', '.character_DA::FL_DEFENSIVE_AFFLICTION.' = '.$char->get_defensive_affliction().', '.character_DA::FL_DESERT_HARVEST.' = '.$char->get_desert_harvest().',
                        '.character_DA::FL_JUNGLE_HARVEST.' = '.$char->get_jungle_harvest().', '.character_DA::FL_LAKE_HARVEST.' = '.$char->get_lake_harvest().', '.character_DA::FL_PRIME_ROOTS_HARVEST.' = '.$char->get_prime_roots_harvest().', '.character_DA::FL_FOREST_HARVEST.' = '.$char->get_forest_harvest().',
                        '.character_DA::FL_CRAFT_HEAVY_ARMOR_BOOT.' = '.$char->get_craft_heavy_armor_boot().', '.character_DA::FL_CRAFT_HEAVY_ARMOR_GLOVE.' = '.$char->get_craft_heavy_armor_glove().', '.character_DA::FL_CRAFT_HEAVY_ARMOR_HELMET.' = '.$char->get_craft_heavy_armor_helmet().', '.character_DA::FL_CRAFT_HEAVY_ARMOR_PANT.' = '.$char->get_craft_heavy_armor_pant().',
                        '.character_DA::FL_CRAFT_HEAVY_ARMOR_SLEEVE.' = '.$char->get_craft_heavy_armor_sleeve().', '.character_DA::FL_CRAFT_HEAVY_ARMOR_VEST.' = '.$char->get_craft_heavy_armor_vest().', '.character_DA::FL_CRAFT_MEDIUM_ARMOR_BOOT.' = '.$char->get_craft_medium_armor_boot().', '.character_DA::FL_CRAFT_MEDIUM_ARMOR_GLOVE.' = '.$char->get_craft_medium_armor_glove().',
                        '.character_DA::FL_CRAFT_MEDIUM_ARMOR_PANT.' = '.$char->get_craft_medium_armor_pant().', '.character_DA::FL_CRAFT_MEDIUM_ARMOR_SLEEVE.' = '.$char->get_craft_medium_armor_sleeve().', '.character_DA::FL_CRAFT_MEDIUM_ARMOR_VEST.' = '.$char->get_craft_medium_armor_vest().', '.character_DA::FL_CRAFT_LIGHT_ARMOR_BOOT.' = '.$char->get_craft_light_armor_boot().',
                        '.character_DA::FL_CRAFT_LIGHT_ARMOR_GLOVE.' = '.$char->get_craft_light_armor_glove().', '.character_DA::FL_CRAFT_LIGHT_ARMOR_PANT.' = '.$char->get_craft_light_armor_pant().', '.character_DA::FL_CRAFT_LIGHT_ARMOR_SLEEVE.' = '.$char->get_craft_light_armor_sleeve().', '.character_DA::FL_CRAFT_LIGHT_ARMOR_VEST.' = '.$char->get_craft_light_armor_vest().',
                        '.character_DA::FL_CRAFT_SHIELD.' = '.$char->get_craft_shield().', '.character_DA::FL_CRAFT_LARGE_SHIELD.' = '.$char->get_craft_large_shield().', '.character_DA::FL_CRAFT_JEWEL_ANKLET.' = '.$char->get_craft_jewel_anklet().', '.character_DA::FL_CRAFT_JEWEL_BRACELET.' = '.$char->get_craft_jewel_bracelet().', '.character_DA::FL_CRAFT_JEWEL_DIADEM.' = '.$char->get_craft_jewel_diadem().',
                        '.character_DA::FL_CRAFT_JEWEL_EARRING.' = '.$char->get_craft_jewel_earring().', '.character_DA::FL_CRAFT_JEWEL_PENDANT.' = '.$char->get_craft_jewel_pendant().', '.character_DA::FL_CRAFT_JEWEL_RING.' = '.$char->get_craft_jewel_ring().', '.character_DA::FL_CRAFT_AXE_1H.' = '.$char->get_craft_axe_1h().',
                        '.character_DA::FL_CRAFT_DAGGER_1H.' = '.$char->get_craft_dagger_1h().', '.character_DA::FL_CRAFT_MACE_1H.' = '.$char->get_craft_mace_1h().', '.character_DA::FL_CRAFT_SPEAR_1H.' = '.$char->get_craft_spear_1h().', '.character_DA::FL_CRAFT_SWORD_1H.' = '.$char->get_craft_sword_1h().',
                        '.character_DA::FL_CRAFT_STAFF_1H.' = '.$char->get_craft_staff_1h().', '.character_DA::FL_CRAFT_AXE_2H.' = '.$char->get_craft_axe_2h().', '.character_DA::FL_CRAFT_MACE_2H.' = '.$char->get_craft_mace_2h().', '.character_DA::FL_CRAFT_PIKE_2H.' = '.$char->get_craft_pike_2h().',
                        '.character_DA::FL_CRAFT_SWORD_2H.' = '.$char->get_craft_sword_2h().', '.character_DA::FL_CRAFT_AMPLI.' = '.$char->get_craft_ampli().', '.character_DA::FL_CRAFT_PISTOL.' = '.$char->get_craft_pistol().', '.character_DA::FL_CRAFT_AUTOLAUNCHER.' = '.$char->get_craft_autolauncher().',
                        '.character_DA::FL_CRAFT_LAUNCHER.' = '.$char->get_craft_launcher().', '.character_DA::FL_CRAFT_RIFLE.' = '.$char->get_craft_rifle().', '.character_DA::FL_LAST_UPDATE_DATE.' = '.$this->format_date_to_sql_param(date('Y-m-d H:i:s',time())).'
                         WHERE '.character_DA::FL_ID.' = '.$id;

//        echo '<br>character_DA->update_character : '.$sql_cmd;

        $this->execute_sql_command($sql_cmd, false);
    }


    /**
     * (PHP 5)<br/>
     * Create guild member in the database
     * @param Guild_Member $member
     * @param int $id_guild
     * @param bool $is_guild_init 
     */
    public function create_guild_member(Guild_Member $member, $id_guild, $is_guild_init){
        //generate random api key, it will be replace by the real one after
        $api_key = 'ZZ'.time().rand(0,99999);
        
        $sql_cmd = 'INSERT INTO '.$this->get_table_prefix().'_character ('.character_DA::FL_NAME.', '.character_DA::FL_GRADE.', '.character_DA::FL_API_KEY.', '.character_DA::FL_FK_GUILD_ID.',
                                            '.character_DA::FL_ACTIVE.', '.character_DA::FL_JOINED_DATE.', '.character_DA::FL_LAST_UPDATE_DATE.')
                    VALUES ('.$this->format_string_to_sql_param($member->get_name()).','.$member->get_grade_id().','
                            .$this->format_string_to_sql_param($api_key).','.$id_guild.',
                        1, '.$this->format_date_to_sql_param($member->get_joined_date()).', '.$this->format_date_to_sql_param(date('Y-m-d H:i:s',time())).')';

//        echo '<br>character_DA->create_guild_member : '.$sql_cmd;

        $this->execute_sql_command($sql_cmd, true);

        if (!$is_guild_init) {
            Logbook::new_member_in_guild($member->get_name(), $id_guild);
        }
    }

    /**
     * (PHP 5)<br/>
     * Delete character in the database
     * @param string $member_name
     * @param int $char_id
     * @param int $id_guild
     */
    public function delete_character_leaving_guild($member_name, $char_id, $id_guild){
        $sql_cmd = 'DELETE FROM '.$this->get_table_prefix().'_character WHERE '.character_DA::FL_ID.' = '.$char_id;

//        echo '<br>character_DA->delete_character_leaving_guild : '.$sql_cmd;
        
        $this->execute_sql_command($sql_cmd, false);
        
        Logbook::member_leave_guild($member_name, $id_guild);
    }

    /**
     * (PHP 5)<br/>
     * Get character information
     * @param int $id
     * @return Character
     */
    public function get_character($id){
        //TODO : change name field name in tables '.$this->get_table_prefix().'_guild and '.$this->get_table_prefix().'_character
        //store the field name because name field has the same name in tables '.$this->get_table_prefix().'_guild and '.$this->get_table_prefix().'_character
        $fl_guild_name = 'guild_name';

        $sql_cmd = 'SELECT c.*, g.'.guild_DA::FL_ICON.', g.'.guild_DA::FL_NAME.' as '.$fl_guild_name.' 
            FROM '.$this->get_table_prefix().'_character c
            INNER JOIN '.$this->get_table_prefix().'_guild g
            ON g.'.guild_DA::FL_ID.' = c.'.character_DA::FL_FK_GUILD_ID.' 
            WHERE c.'.character_DA::FL_ID.' = '.$id;

//        echo '<br>character_DA->get_character : '.$sql_cmd;

        $char_array = $this->execute_sql_command($sql_cmd, false);
        

        if (isset($char_array[0])){
            $char = $char_array[0];
//
//            echo var_dump($member[$fl_guild_name]).'<br>';
//            echo var_dump($member[guild_DA::FL_ICON]).'<br>';
//

            $character = new Character();




            $character->set_general_information($char[character_DA::FL_ID], $char[character_DA::FL_NAME], $char[character_DA::FL_SHARD], $char[character_DA::FL_RACE], $char[character_DA::FL_GENDER],
                    $char[character_DA::FL_TITLEID], $char[character_DA::FL_MONEY], $char[character_DA::FL_GRADE], $char[character_DA::FL_JOINED_DATE], $char[character_DA::FL_LAST_UPDATE_DATE],
                    $char[$fl_guild_name], $char[guild_DA::FL_ICON]);

            $character->set_fight_skill($char[character_DA::FL_MACE_1H], $char[character_DA::FL_STAFF_1H], $char[character_DA::FL_SPEAR_1H], $char[character_DA::FL_AXE_1H],
                    $char[character_DA::FL_SWORD_1H], $char[character_DA::FL_DAGGER_1H], $char[character_DA::FL_MACE_2H], $char[character_DA::FL_PIKE_2H], $char[character_DA::FL_AXE_2H],
                    $char[character_DA::FL_SWORD_2H], $char[character_DA::FL_HAND_TO_HAND], $char[character_DA::FL_PISTOL], $char[character_DA::FL_AUTOLAUNCHER],
                    $char[character_DA::FL_LAUNCHER], $char[character_DA::FL_RIFLE]);

            $character->set_magic_skill($char[character_DA::FL_ELEMENTAL_MAGIC], $char[character_DA::FL_OFFENSIVE_AFFICTION], $char[character_DA::FL_HEALING_MAGIC],
                    $char[character_DA::FL_DEFENSIVE_AFFLICTION]);

            $character->set_harvest_skill($char[character_DA::FL_DESERT_HARVEST], $char[character_DA::FL_JUNGLE_HARVEST], $char[character_DA::FL_LAKE_HARVEST],
                    $char[character_DA::FL_PRIME_ROOTS_HARVEST], $char[character_DA::FL_FOREST_HARVEST]);

            $character->set_craft_armor_item_skill($char[character_DA::FL_CRAFT_HEAVY_ARMOR_BOOT], $char[character_DA::FL_CRAFT_HEAVY_ARMOR_GLOVE], $char[character_DA::FL_CRAFT_HEAVY_ARMOR_HELMET],
                    $char[character_DA::FL_CRAFT_HEAVY_ARMOR_PANT], $char[character_DA::FL_CRAFT_HEAVY_ARMOR_SLEEVE], $char[character_DA::FL_CRAFT_HEAVY_ARMOR_VEST], $char[character_DA::FL_CRAFT_MEDIUM_ARMOR_BOOT],
                    $char[character_DA::FL_CRAFT_MEDIUM_ARMOR_GLOVE], $char[character_DA::FL_CRAFT_MEDIUM_ARMOR_PANT], $char[character_DA::FL_CRAFT_MEDIUM_ARMOR_SLEEVE], $char[character_DA::FL_CRAFT_MEDIUM_ARMOR_VEST],
                    $char[character_DA::FL_CRAFT_LIGHT_ARMOR_BOOT], $char[character_DA::FL_CRAFT_LIGHT_ARMOR_GLOVE], $char[character_DA::FL_CRAFT_LIGHT_ARMOR_PANT], $char[character_DA::FL_CRAFT_LIGHT_ARMOR_SLEEVE],
                    $char[character_DA::FL_CRAFT_LIGHT_ARMOR_VEST], $char[character_DA::FL_CRAFT_SHIELD], $char[character_DA::FL_CRAFT_LARGE_SHIELD]);

            $character->set_craft_jewel_item_skill($char[character_DA::FL_CRAFT_JEWEL_ANKLET], $char[character_DA::FL_CRAFT_JEWEL_BRACELET], $char[character_DA::FL_CRAFT_JEWEL_DIADEM],
                    $char[character_DA::FL_CRAFT_JEWEL_EARRING], $char[character_DA::FL_CRAFT_JEWEL_PENDANT], $char[character_DA::FL_CRAFT_JEWEL_RING]);

            $character->set_craft_weapon_skill($char[character_DA::FL_CRAFT_AXE_1H], $char[character_DA::FL_CRAFT_DAGGER_1H], $char[character_DA::FL_CRAFT_MACE_1H], $char[character_DA::FL_CRAFT_SPEAR_1H],
                    $char[character_DA::FL_CRAFT_SWORD_1H], $char[character_DA::FL_CRAFT_STAFF_1H], $char[character_DA::FL_CRAFT_AXE_2H], $char[character_DA::FL_CRAFT_MACE_2H],
                    $char[character_DA::FL_CRAFT_PIKE_2H], $char[character_DA::FL_CRAFT_SWORD_2H], $char[character_DA::FL_CRAFT_AMPLI], $char[character_DA::FL_CRAFT_PISTOL],
                    $char[character_DA::FL_CRAFT_AUTOLAUNCHER], $char[character_DA::FL_CRAFT_LAUNCHER], $char[character_DA::FL_CRAFT_RIFLE]);

            $character->set_statistics($char[character_DA::FL_CONSTITUTION], $char[character_DA::FL_METABOLISM], $char[character_DA::FL_INTELLIGENCE], $char[character_DA::FL_WISDOM],
                    $char[character_DA::FL_STRENGTH], $char[character_DA::FL_WELLBALANCED], $char[character_DA::FL_DEXTERITY], $char[character_DA::FL_WILL], $char[character_DA::FL_HITPOINTS],
                    $char[character_DA::FL_STAMINA], $char[character_DA::FL_SAP], $char[character_DA::FL_FOCUS]);

            $character->set_fame($char[character_DA::FL_CIVILIZATION], $char[character_DA::FL_CULT], $char[character_DA::FL_FYROS_FAME], $char[character_DA::FL_MATIS_FAME],
                    $char[character_DA::FL_TRYKER_FAME], $char[character_DA::FL_ZORAI_FAME], $char[character_DA::FL_KAMI_FAME], $char[character_DA::FL_KARAVAN_FAME],
                    $char[character_DA::FL_TRIBE_DARKENING_SAP_FAME], $char[character_DA::FL_TRIBE_LAWLESS_FAME], $char[character_DA::FL_TRIBE_LEVIERS_FAME],
                    $char[character_DA::FL_TRIBE_MASTER_OF_THE_GOO_FAME], $char[character_DA::FL_TRIBE_RESTORERS_FAME], $char[character_DA::FL_TRIBE_SCORCHERS_FAME], $char[character_DA::FL_TRIBE_WATER_BREAKERS_FAME]);

            $character->set_faction_point($char[character_DA::FL_FYROS_FACTION_POINT], $char[character_DA::FL_MATIS_FACTION_POINT], $char[character_DA::FL_TRYKER_FACTION_POINT],
                    $char[character_DA::FL_ZORAI_FACTION_POINT], $char[character_DA::FL_KAMI_FACTION_POINT], $char[character_DA::FL_KARAVAN_FACTION_POINT]);

            $character->set_connection_information($char[character_DA::FL_PLAYED_TIME], $char[character_DA::FL_LATEST_LOGIN], $char[character_DA::FL_LATEST_LOGOUT], null);

            $character->set_body_information($char[character_DA::FL_HAIR_TYPE], $char[character_DA::FL_HAIR_COLOR], $char[character_DA::FL_TATTOO], $char[character_DA::FL_EYES_COLOR],
                    $char[character_DA::FL_GABARIT_HEIGHT], $char[character_DA::FL_GABARIT_TORSO_WIDTH], $char[character_DA::FL_GABARIT_ARMS_WIDTH], $char[character_DA::FL_GABARIT_LEGS_WIDTH],
                    $char[character_DA::FL_GABARIT_BREAST_SIZE], $char[character_DA::FL_MORPH1], $char[character_DA::FL_MORPH2], $char[character_DA::FL_MORPH3], $char[character_DA::FL_MORPH4],
                    $char[character_DA::FL_MORPH5], $char[character_DA::FL_MORPH6], $char[character_DA::FL_MORPH7], $char[character_DA::FL_MORPH8]);
        }

        return (isset($character) ? $character : null);
    }

    /**
     * (PHP 5)<br/>
     * Get the list of guild member. It returns an array of Guild_Member 
     * @param int $guild_id
     * @return array of Guild_Member
     */
    public function get_guild_member_list($guild_id){
        //TODO : change name field name in tables '.$this->get_table_prefix().'_guild and '.$this->get_table_prefix().'_character
        //store the field name because name field has the same name in tables '.$this->get_table_prefix().'_guild and '.$this->get_table_prefix().'_character
        $fl_guild_name = 'guild_name';

        $sql_cmd = 'SELECT c.'.character_DA::FL_ID.', c.'.character_DA::FL_NAME.', c.'.character_DA::FL_GRADE.',
                    c.'.character_DA::FL_JOINED_DATE.', c.'.character_DA::FL_LAST_UPDATE_DATE.',
                        g.'.guild_DA::FL_ICON.', g.'.guild_DA::FL_NAME.' as '.$fl_guild_name.'
                        FROM '.$this->get_table_prefix().'_character c
                        INNER JOIN '.$this->get_table_prefix().'_guild g
                        ON g.'.guild_DA::FL_ID.' = c.'.character_DA::FL_FK_GUILD_ID.' 
                        WHERE c.'.character_DA::FL_FK_GUILD_ID.' = '.$guild_id;

//        echo '<br>character_DA->get_guild_member_list : '.$sql_cmd;

        $result = $this->execute_sql_command($sql_cmd, false);

        foreach($result as $member) {
            $m = new Guild_Member();
            $m->set_general_information($member[character_DA::FL_ID], $member[character_DA::FL_NAME], 
                    $member[character_DA::FL_GRADE], $member[character_DA::FL_JOINED_DATE], $member[character_DA::FL_LAST_UPDATE_DATE],
                    $member[$fl_guild_name], $member[guild_DA::FL_ICON]);
            $member_list[] = $m;
        }

        return $member_list;
        
    }

 /**
     * (PHP 5)<br/>
     * Check if a character exists in the database
     * @param string $api_key
     * @return bool
     */
    public function character_exists($api_key){
        $sql_cmd = 'SELECT count(*) FROM '.$this->get_table_prefix().'_character WHERE '.character_DA::FL_API_KEY.' = '.$this->format_string_to_sql_param($api_key);

        $test = $this->execute_sql_command($sql_cmd, false);

//        echo '<br>character_DA->guild_exists : '.$sql_cmd;

        if ((count($test) == 0) || ($test[0][0] == 0)) return false;

        return true;
    }

    /**
     * (PHP 5)<br/>
     * Update character api key
     * @param int $char_id
     * @param string $api_key
     */
    public function update_character_api_key($char_id, $api_key){
        $sql_cmd = 'UPDATE '.$this->get_table_prefix().'_character
                    SET '.character_DA::FL_API_KEY.' = '.$this->format_string_to_sql_param($api_key).'
                     WHERE '.character_DA::FL_ID.' = '.$char_id;

//        echo '<br>character_DA->update_character_api_key : '.$sql_cmd;

        $this->execute_sql_command($sql_cmd, false);
    }


    /**
     * (PHP 5)<br/>
     * Save character
     * @param Character $character
     */
    public function save_character(Character $character){
        $id = null;

        $id = $character->get_id() == null ? $this->get_character_id($character->get_name()) : $character->get_id();

        if ($id != null) {
            $this->update_character($character, $id);
            $this->update_character_api_key($id, $character->get_api_key());
        }
        else {
            echo 'The character don\'t belong to a guild in DB. Updating the DB information can solve the problem';
        }

    }
    
}
?>
